import pandas as pd
import os
import time
import re
import plotly.graph_objs as go
import xlwings as xw


class hand_port:
    def __init__(self):
        self.path = None
        self.date = []
        list_path = []

        files = os.listdir('./')
        for file in files:
            if 'yield' in file:
                list_path.append(file)
        self.path = list_path

    def clear_data(self):
        list_data = []
        list_date = []
        for i in self.path:
            df = pd.read_excel(i, header=1, sheet_name='Main', usecols=[0, 4])
            pattern = r"\d{4}(?=\d{6}\.)"
            match = re.search(pattern, i)
            if match:
                number = match.group()
                df.columns = ['Station Name', str(number)]
                list_date.append(number)
            list_data.append(df)
        df2 = list_data[0]
        for df in list_data[1:]:
            df2 = pd.merge(df, df2, on=['Station Name'], how='outer')

        df2[list_date] = df2[list_date].astype('float')  # 将数据转换为浮点数，方便绘图，和判断转化为字符串
        list_date.sort()
        self.plot_timechange(df2, list_date)
        list_new_columns = ['Station Name']
        for i in list_date:
            list_new_columns.append(i)

        df2 = df2.reindex(list_new_columns, axis=1)
        df2 = df2.apply(lambda x: ('%.2f%%' % (x * 100)) if isinstance(x, float) else x)
        # print(df2)
        df2.to_excel('一周设备良率表.xlsx', index=False, sheet_name='yield')
        with xw.App(visible=False) as app:
            wb = app.books.open(f'一周设备良率表.xlsx')
            sht = wb.sheets[0]
            sht.autofit()
            wb.save()

    def plot_timechange(self, df2, list2):
        fig = go.Figure()
        for index in list2:
            fig.add_scatter(y=df2[index],
                            x=df2['Station Name'],

                            name=index,
                            mode='markers+lines',
                            connectgaps=True,
                            marker={"showscale": True}
                            )
        fig.update_layout(

            title={'text': '一周良率图 ', 'x': 0.5, "font": {"family": "STKaiti", "size": 30}, },
            xaxis={'title': 'SN'},
            paper_bgcolor='rgb(243, 243, 243)',
            template='seaborn',  # ggplot2,gridon,plotly_dark,seaborn,xgridoff #设置不同的模版背景
            legend_title_text='设备标识',
            yaxis=dict(
                title='SN_Yield',
                tickangle=0,  # 设置刻度值的旋转角度为45度
                # categoryorder='array',  # 设置刻度模式为线性
                # autorange = True,
                # dtick = 0.0005,
                tickvals=[0.991, 0.992, 0.993, 0.994, 0.995, 0.996, 0.997, 0.9975, 0.998, 0.99825, 0.9985, 0.99875,
                          0.999, 0.99925, 0.9995, 0.99975, 1],
                # 自定义刻度值
                ticktext=['99.10%', '99.20%', '99.30%', '99.40%', '99.50%', '99.60%', '99.70%', '99.75%', '99.80%',
                          '99.825%', '99.85%', '99.875%', '99.90%', '99.925', '99.95%', '99.975',
                          '100%'],
                tickformat=".2%",
            )
        )
        fig.write_html(f"一周设备良率.html")
        fig.show()


if __name__ == '__main__':
    start = time.time()
    s = hand_port()
    s.clear_data()
    print(f'程序运行共花费{round(time.time() - start, 3)}秒')
